﻿IF EXISTS (
    SELECT
      1
    FROM sysobjects
    WHERE id = OBJECT_ID('bd_proc_report_group_attendance')
      AND type IN ('P', 'PC')
  )
  DROP PROCEDURE bd_proc_report_group_attendance

GO

-- =============================================
-- Author:      YFC
-- Create date: 2020-07-31 11:25:18
-- Database:    [scm_main-hysj2]
-- Description: 移动端-管理员考勤查询
-- =============================================

CREATE PROCEDURE bd_proc_report_group_attendance
  @groupId INT
 ,@shiftNo VARCHAR(30)
 ,@beginDate DATETIME
 ,@endDate DATETIME
 ,@userNoOrName VARCHAR(200)
AS
BEGIN
  SET NOCOUNT ON;

  CREATE TABLE #result
  (
    user_serial BIGINT,
    user_no VARCHAR(20),
    user_name VARCHAR(100),
    schedule_date DATE,
    att_result VARCHAR(20),  --考勤结果
    overtime_min INT DEFAULT 0,    --加班时长（分钟）
    overtime_hour DECIMAL(8,2)  DEFAULT 0,  --加班时长（小时）
    shift_name VARCHAR(50)   --班次
  )

  DECLARE @shiftId INT, @sql VARCHAR(MAX) = ''

  SELECT @shiftId = bc_id FROM bd_view_bc_sd WHERE bh = @shiftNo

  SELECT @sql = '
    INSERT INTO #result (user_serial, user_no, user_name, schedule_date, shift_name)
    SELECT emp.user_serial, u.user_no, u.user_lname, emp.schedule_date, shift.mc
    FROM bd_schedule_group_order_detail emp
    INNER JOIN bd_kq_schedule_detail sch ON sch.user_serial = emp.user_serial AND emp.schedule_date = sch.shift_date
    INNER JOIN bd_view_bc_sd shift ON shift.bc_id = sch.shift_id
    INNER JOIN (
        SELECT
          user_serial, user_no, user_lname, user_dep, dep_name
        FROM bd_view_user_all
      ) u ON u.user_serial = emp.user_serial
    WHERE 1 = 1 '
  
  SELECT @sql += ' AND emp.group_id = ' + CAST(@groupId AS VARCHAR(20))

  SELECT @sql += ' AND emp.schedule_date >= ''' + CONVERT(VARCHAR(10), @beginDate, 120) + ''' AND emp.schedule_date < ''' + CONVERT(VARCHAR(10), DATEADD(DAY, 1, @endDate)) + ''' '

  IF ISNULL(@shiftId, 0) > 0 
  BEGIN  
    SELECT @sql += ' AND sch.shift_id = ' + CAST(@shiftId AS VARCHAR(20))
  END
  
  IF ISNULL(@userNoOrName, '') != '' 
  BEGIN  
  	SELECT @sql += 'AND (u.user_no LIKE ''%' + @userNoOrName + '%'' OR u.user_lname LIKE ''%' + @userNoOrName + '%'')'
  END
  
  EXEC(@sql)


  UPDATE r
  SET overtime_min = card.ot_pending_hours
     ,att_result =
        CASE card.status
          WHEN 4 THEN
            '迟到'
          WHEN 5 THEN
            '早退'
          WHEN 6 THEN
            '缺勤'
          ELSE
            '正常'
        END
  FROM #result r
  INNER JOIN bd_kq_timecard card ON card.user_serial = r.user_serial AND r.schedule_date = card.card_date
  WHERE card.card_date >= @beginDate AND card.card_date <= @endDate

  UPDATE #result SET overtime_hour = CAST(overtime_min / 60.0 AS DECIMAL(8,2))

  SELECT * FROM #result

  DROP TABLE #result

  SET NOCOUNT OFF;
END